package com.hulu.yec.sys.course.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang3.StringUtils;

import com.hulu.yec.common.Constant;
import com.hulu.yec.common.PageModel;
import com.hulu.yec.common.util.DBUtil;
import com.hulu.yec.common.util.DateUtil;
import com.hulu.yec.common.util.LogUtil;
import com.hulu.yec.sys.course.entity.CourseEntity;

/**
 * 课程dao层
 * 
 * @author Administrator
 *
 */
public class CourseDao {




	/**
	 * 查询所有课程（表格）
	 * 
	 * @param pageSize
	 *            一页显示多少条数据
	 * @param pageNo
	 *            从多少条数据开始
	 * @return
	 */
	public PageModel<CourseEntity> courseList(CourseEntity courseEntity,Integer pageNo, Integer pageSize) {
		// 创建存储课程信息的集合对象
				List<CourseEntity> courseList = new ArrayList<>();
				PageModel<CourseEntity> pageModel  = new PageModel<CourseEntity>(courseList, pageNo, pageSize, getAllRecords(courseEntity));
				//向SQL添加参数的集合
				List<Object> paramList = new ArrayList<Object>();
				StringBuilder sb = new StringBuilder();
				sb.append("select * from sys_course WHERE state <> 'null' ");
				if(StringUtils.isNotBlank(courseEntity.getName())){
					sb.append(" AND name like ? ");
					paramList.add("%" + courseEntity.getName() + "%");
				}
				if(StringUtils.isNotBlank(courseEntity.getOperateUserId())){
					sb.append(" AND operate_user_Id = ? ");
					paramList.add(courseEntity.getOperateUserId());
				}
				if(courseEntity.getState() != 0){
					sb.append(" AND state = ? ");
					paramList.add(courseEntity.getState());
				}
				sb.append("order by id desc ");
				sb.append("limit ?, ? ");
				paramList.add((pageNo - 1) * pageSize);
				paramList.add( pageSize);
				// 数据库相关操作
				Connection connection = null;
				PreparedStatement preparedStatement = null;
				ResultSet resultSet = null;
				try {
					connection = DBUtil.getConnection();
					// 通过连接对象创建编译对象
					preparedStatement = connection.prepareStatement(sb.toString());
					// 设置值
					if(paramList != null){
						for ( int i = 0; i < paramList.size(); i++){
							preparedStatement.setObject(i+1, paramList.get(i));
						}
					}
					// 通过编译对象执行SQL操作
					resultSet = preparedStatement.executeQuery();

					// 判断内容是否存在
					if (resultSet != null) {
						while (resultSet.next()) {
							// 创建一个课程信息对象并把查询到的结果封装到相对应的属性中
							CourseEntity course = new CourseEntity();
							course.setId(resultSet.getInt("id"));
							course.setPId(resultSet.getString("pid"));
							course.setIsLeaf(resultSet.getInt("is_leaf"));
							course.setName(resultSet.getString("name"));
							course.setOperateUserId(resultSet.getString("operate_user_id"));
							course.setOperateTime(resultSet.getDate("operate_time"));
							course.setState(resultSet.getInt("state"));
							course.setMemo(resultSet.getString("memo"));
							// 把封装好的信息对象添加到课程信息集合中
							courseList.add(course);
						}
					}
					return pageModel;
				} catch (Exception e) {
					LogUtil.i(Constant.LOGGER, "sql错误");
					courseList = Collections.emptyList();
				} finally {
					DBUtil.close(connection, preparedStatement, resultSet);
				}
				return null;
	}

	/**
	 * 得到记录数
	 * 
	 * @return
	 */
	public int getAllRecords(CourseEntity courseEntity) {
		List<Object> paramList = new ArrayList<Object>();
		// 定义一个row返回查询到的总记录数
		Integer row = 0;
		
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT COUNT(*) as 'count' from sys_course WHERE state <> 'null' ");
		if(StringUtils.isNotBlank(courseEntity.getName())){
			sb.append(" AND name like ? ");
			paramList.add("%" + courseEntity.getName() + "%");
		}
		if(StringUtils.isNotBlank(courseEntity.getOperateUserId())){
			sb.append(" AND operate_user_Id = ?");
			paramList.add(courseEntity.getOperateUserId());
		}
		if(courseEntity.getState() != 0){
			sb.append(" AND state = ? ");
			paramList.add(courseEntity.getState());
		}
		sb.append("order by id desc ");
		
		// 数据库相关操作
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = DBUtil.getConnection();
			// 通过连接对象创建编译对象
			preparedStatement = connection.prepareStatement(sb.toString());
			//设置参数
			if(paramList != null){
				for ( int i = 0; i < paramList.size(); i++){
					preparedStatement.setObject(i+1, paramList.get(i));
				}
			}
			// 通过编译对象执行SQL指令
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				row = resultSet.getInt("count");
			}
		} catch (Exception e) {
			LogUtil.i(Constant.LOGGER, "sql错误");
			e.printStackTrace();
		} finally {
			DBUtil.close(connection, preparedStatement, resultSet);
		}
		return row;
	}

	
	/**
	 * 查询树形菜单的所有课程
	 * 
	 * @return
	 */
	public List<CourseEntity> courseAllMenuList() {
		List<CourseEntity> courseChildMenuList = new ArrayList<>();
		String  sql = ("select * from sys_course ");
		// 数据库相关操作
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
				connection = DBUtil.getConnection();
				// 通过连接对象创建编译对象
				preparedStatement = connection.prepareStatement(sql);
				// 通过编译对象执行SQL操作
				resultSet = preparedStatement.executeQuery();
	
				// 判断内容是否存在
				if (resultSet != null) {
					while(resultSet.next()){
						// 创建一个课程信息对象并把查询到的结果封装到相对应的属性中
						CourseEntity course = new CourseEntity();
						course.setId(resultSet.getInt("id"));
						course.setPId(resultSet.getString("pid"));
						course.setIsLeaf(resultSet.getInt("is_leaf"));
						course.setName(resultSet.getString("name"));
						course.setOperateUserId(resultSet.getString("operate_user_id"));
						course.setOperateTime(resultSet.getDate("operate_time"));
						course.setState(resultSet.getInt("state"));
						course.setMemo(resultSet.getString("memo"));
						courseChildMenuList.add(course);
					}
				}
			} catch (Exception e) {
				e.printStackTrace();
				LogUtil.i(Constant.LOGGER, "sql错误");
			} finally {
				DBUtil.close(connection, preparedStatement, resultSet);
			}
			return courseChildMenuList;
	}

	
	
	/**
	 * 新增课程
	 * 
	 * @param courseInsert
	 * @return 返回row:来判断新增是否成功
	 */
	public int addCourse(CourseEntity courseInsert) {
		//定义一个变量来判断新增是否成功
		Integer row = 0;
		//sql:要执行的sql语句
		String sql = "INSERT into sys_course values(null,?,?,?,1,now(),?,?) ;";
		//定义数据库相关句柄
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			//连接数据库
			conn = DBUtil.getConnection();
			//通过连接对象创建编译对象
			ps = conn.prepareStatement(sql);
			//设置参数
			ps.setString(1, courseInsert.getPId());
			ps.setInt(2, courseInsert.getIsLeaf());
			ps.setString(3, courseInsert.getName());
			//ps.setString(4, courseInsert.getOperateUserId());
			ps.setInt(4, courseInsert.getState());
			ps.setString(5, courseInsert.getMemo());
			//通过编译对象执行SQL操作
			row = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			LogUtil.i(Constant.LOGGER, "sql错误");
		} finally {
			try {
					if(ps !=null ){
						ps.close();
					}
					if(conn != null && !conn.isClosed()){
						conn.close();
						conn = null;
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		
		return row;
	}

	/**
	 * 查询所有的pId：用于新增页面的查询
	 * @return
	 */
	public List<CourseEntity> pIdList() {
		//定义一个List来存储查询出来的pId
		List<CourseEntity> pIdList = new ArrayList<>();
		//SQL语句
		String  sql = "SELECT id,name from sys_course ";
		// 数据库相关操作
				Connection connection = null;
				PreparedStatement preparedStatement = null;
				ResultSet resultSet = null;
				try {
						connection = DBUtil.getConnection();
						// 通过连接对象创建编译对象
						preparedStatement = connection.prepareStatement(sql);
						// 通过编译对象执行SQL操作
						resultSet = preparedStatement.executeQuery();
			
						// 判断内容是否存在
						if (resultSet != null) {
							while(resultSet.next()){
								// 创建一个课程信息对象并把查询到的结果封装到相对应的属性中
								CourseEntity course = new CourseEntity();
								course.setId(resultSet.getInt("id"));
								course.setName(resultSet.getString("name"));
								pIdList.add(course);
							}
						}
					} catch (Exception e) {
						e.printStackTrace();
						LogUtil.i(Constant.LOGGER, "sql错误");
					} finally {
						DBUtil.close(connection, preparedStatement, resultSet);
					}
					return pIdList;
	}

	/**
	 * 显示父节点名字
	 * 
	 * @param ids 父节点id
	 * @return	父节点名字
	 */
	public List<CourseEntity> getName(List<Integer> ids) {
		List<CourseEntity> list=new ArrayList<CourseEntity>();
		for(int i=0;i<ids.size();i++){
			String sql="select name from sys_course where id =?";
			Connection connection = null;
			PreparedStatement preparedStatement = null;
			ResultSet resultSet = null;
			try {
					connection = DBUtil.getConnection();
					// 通过连接对象创建编译对象
					preparedStatement = connection.prepareStatement(sql);
					preparedStatement.setInt(1, ids.get(i));
					// 通过编译对象执行SQL操作
					resultSet = preparedStatement.executeQuery();
		
					// 判断内容是否存在
					if (resultSet != null) {
						while(resultSet.next()){
							// 创建一个课程信息对象并把查询到的结果封装到相对应的属性中
							CourseEntity course = new CourseEntity();
							course.setName(resultSet.getString("name"));
							list.add(course);
						}
					}
				} catch (Exception e) {
					e.printStackTrace();
					LogUtil.i(Constant.LOGGER, "sql错误");
				} finally {
					DBUtil.close(connection, preparedStatement, resultSet);
				}
		
		}
		return list;
	}

	/**
	 * 修改（1）：根据id查询出当前id的课程信息
	 * @param id 
	 * 
	 * @return
	 * 				传来id的课程信息
	 */
	public CourseEntity courseIntiUpdateList(Integer id) {
		//定义一个实体对象courseListInti：接收根据id查询出来的课程信息
		CourseEntity  courseListInti = new CourseEntity();
		//SQL
		String sql = "select * from  sys_course where id =? ";
		// 数据库相关操作
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
				connection = DBUtil.getConnection();
				// 通过连接对象创建编译对象
				preparedStatement = connection.prepareStatement(sql);
				//设置参数
				preparedStatement.setInt(1, id);
				// 通过编译对象执行SQL操作
				resultSet = preparedStatement.executeQuery();
				// 判断内容是否存在
				if (resultSet != null) {
					while(resultSet.next()){
						// 创建一个课程信息对象并把查询到的结果封装到相对应的属性中
						courseListInti.setId(resultSet.getInt("id"));
						courseListInti.setPId(resultSet.getString("pid"));
						courseListInti.setIsLeaf(resultSet.getInt("is_leaf"));
						courseListInti.setName(resultSet.getString("name"));
						courseListInti.setOperateUserId(resultSet.getString("operate_user_id"));
						courseListInti.setOperateTime(resultSet.getDate("operate_time"));
						courseListInti.setState(resultSet.getInt("state"));
						courseListInti.setMemo(resultSet.getString("memo"));
					}
				}
			} catch (Exception e) {
				e.printStackTrace();
				LogUtil.i(Constant.LOGGER, "sql错误");
			} finally {
				DBUtil.close(connection, preparedStatement, resultSet);
			}
		
		return courseListInti;
	}

	/**
	 * 修改（2）：修改用户信息
	 * @param course 修改后的用户信息
	 * @param id 要修改的当前用户的id
	 * 
	 * @return	修改后一条用户信息实体
	 */
	public int courseUpdate(CourseEntity course, Integer id) {
		//受影响的行数
		int row = 0;
		//SQL
		String sql = "UPDATE sys_course  set pid =? ,is_leaf=?, name=?, operate_user_id=?, state=?, memo=? where id=? ";
		//定义数据库相关句柄
				Connection conn = null;
				PreparedStatement ps = null;
				try {
					//连接数据库
					conn = DBUtil.getConnection();
					//通过连接对象创建编译对象
					ps = conn.prepareStatement(sql);
					//设置参数
					ps.setString(1, course.getPId());
					ps.setInt(2, course.getIsLeaf());
					ps.setString(3, course.getName());
					ps.setString(4, course.getOperateUserId());
					ps.setInt(5, course.getState());
					ps.setString(6, course.getMemo());
					ps.setInt(7, id);
					//通过编译对象执行SQL操作
					row = ps.executeUpdate();
				} catch (Exception e) {
					e.printStackTrace();
					LogUtil.i(Constant.LOGGER, "sql错误");
				} finally {
					try {
							if(ps !=null ){
								ps.close();
							}
							if(conn != null && !conn.isClosed()){
								conn.close();
								conn = null;
							}
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
		return row;
	}

	/**
	 * 根据id删除相对应的课程信息
	 * 
	 * @param id		当前要删除的id
	 * @return			返回受影响的行数
	 */
	public int courseDelete(Integer id) {
		//定义一个变量
		int row = 0;
		//SQL
		String sql ="DELETE from sys_course where id= ? ";
		//定义数据库相关句柄
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			//连接数据库
			conn = DBUtil.getConnection();
			//通过连接对象创建编译对象
			ps = conn.prepareStatement(sql);
			//设置参数
			ps.setInt(1,id);
			//通过编译对象执行SQL操作
			row = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			LogUtil.i(Constant.LOGGER, "sql错误");
		} finally {
			try {
					if(ps !=null ){
						ps.close();
					}
					if(conn != null && !conn.isClosed()){
						conn.close();
						conn = null;
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		return row;
	}
	
}
